
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * This class creates  an interface for an agency or an Employer
 * Final Project for TCSS 445.
 * 
 * @author Sanjeev Kamboj
 */
public class SqlDao {
	private Connection my_con;
	
	public SqlDao(final Connection the_con) {
		my_con = the_con;
	}
	
	//Returns a list of records
	protected Map<Integer, Record> getFacultyByExp(final String the_query) {
		ResultSet result = null;
		//List<Record> result_list = null;
		
		String str = "Select * From Faculty_UWT" +
				      " where (HomePageExists = 1) And (Expertise Like ?" +
				       "OR FocusArea Like ?) ";
				 
		try {
			PreparedStatement statement = my_con.prepareStatement(str);
			
			statement.setString(1,"%" + the_query + "%");
			statement.setString(2, "%" + the_query + "%");
			result = statement.executeQuery();
			
					
		} catch (SQLException e) {
			System.err.println("Error in getFacultyByExp");
			e.printStackTrace();
		}
		 
		return findRecords(result, the_query);
		
		
	}
	
//Returns a list of records
	protected Map<Integer, Record> getFacultyByName(String the_name) {
		ResultSet result = null;
		//List<Record> result_list = null;
		
		String str = "Select * From Faculty_UWT" +
				      " where Faculty_UWT.Name Like ?";
				 
		try {
			PreparedStatement statement = my_con.prepareStatement(str);
			
			statement.setString(1, "%" + the_name + "%");
			
			result = statement.executeQuery();
					
		} catch (SQLException e) {
			System.err.println("Error in getFacultyByName");
			e.printStackTrace();
		}
		 
		return findRecords(result, the_name);
		
		
	}
	
	//Returns a list of records
		protected Map<Integer, Record> getFacultyByDept(final String the_dept) {
			ResultSet result = null;
			//List<Record> result_list = null;
			
			String str = "Select * From Faculty_UWT" +
					      " where Faculty_UWT.Department Like ?";
					 
			try {
				PreparedStatement statement = my_con.prepareStatement(str);
				
				statement.setString(1, "%" + the_dept + "%");
				
				result = statement.executeQuery();
						
			} catch (SQLException e) {
				System.err.println("Error in getFacultyByDept");
				e.printStackTrace();
			}
			 
			return findRecords(result, the_dept);
			
		}
		
	
	//Returns a list of records
	protected ResultSet getAllHomePages() {
		ResultSet result = null;
		//List<Record> result_list = null;
		
		String str = "Select ID, Homepage From Faculty_UWT " +
				      "Where Faculty_UWT.HomePageExists =1";
		 
		try {
			PreparedStatement statement = my_con.prepareStatement(str);
			
			//statement.setDouble(1, 1);
			result = statement.executeQuery();
					
		} catch (SQLException e) {
			System.err.println("Error in getEmployer.");
			e.printStackTrace();
		}
		 
		return result;
		
		
	}
	
	//Helper method that returns a list of the strings matching query terms
	private Map<Integer, Record> findRecords(final ResultSet the_result, String the_query) {
		Map<Integer, Record> found_list = new HashMap<Integer, Record>();
		try {
			while(the_result.next()) {
				final int id= Integer.parseInt(the_result.getString(1));  
						
				//if(exp.contains(the_query)) {
			    Record r = new Record(id, 
						              the_result.getString(2),
		                              the_result.getString(5),
		                              the_result.getString(4),
		                              the_result.getString(6),
		                              the_result.getString(7),
		                              the_result.getString(8),
		                              the_result.getString(9),
		                              the_result.getString(10));
					
			    
				found_list.put(id, r);
				
			}
			
		} catch (SQLException e) {
			System.err.println("Error in findRecords");
			e.printStackTrace();
		}
		
		return found_list;
		
		
	}
	
	protected void insertHtmlText(final int id, final String text) {
		
		try {
			PreparedStatement statement = my_con.prepareStatement("INSERT INTO HtmlText VALUES (?,?)");
			statement.setDouble(1, id);
			statement.setString(2, text);
			
			statement.executeUpdate();
		
		} catch (SQLException e) {
			System.err.println("Error in insertHTMLText");
			e.printStackTrace();
		}

	}
	
	//Returns a list of records
	protected String getTextFromId(final int the_id) {
		ResultSet result = null;
		String text_body = "";
		//List<Record> result_list = null;
		
		String str = "Select textBody From HtmlText " +
				      "Where HtmlText.textID = ?";
				 
		try {
			PreparedStatement statement = my_con.prepareStatement(str);
			
			statement.setDouble(1, the_id);
			result = statement.executeQuery();
			
			if(result != null) {
				while(result.next()) {
					 text_body = result.getString(1);
				 }
			}
					
		} catch (SQLException e) {
			System.err.println("Error in getFacultyByExp");
			e.printStackTrace();
			
		}
		return  text_body;
		
	}


}

